package seatechit.msbgateway.dbaccess;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SpringFrameworkDAO {

	public Connection getConnection() {
		Connection conn = null;
		try {
			// Load the JDBC driver
			String driverName = "oracle.jdbc.driver.OracleDriver";
			Class.forName(driverName);
			// Create a connection to the database
			// String serverName = "10.1.17.43";
			// String portNumber = "1521";
			// String sid = "ibprod";

			String serverName = "10.0.2.58";
			String portNumber = "1521";
			String sid = "SMSDB";

			String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
			String username = "BEC";
			String password = "bec";
			conn = DriverManager.getConnection(url, username, password);

			if (conn != null) {
				conn.setAutoCommit(false);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return conn;
	}

	protected ResultSet executeQuery(String sql) throws Exception {
		ResultSet rs = null;
		Connection conn = getConnection();
		if (conn != null) {
			try {
				Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
				rs = stmt.executeQuery(sql);
			} catch (Exception e) {
				e.printStackTrace();

			}
		}
		return rs;
	}

	public Connection getDb2CoreJDBC(String host, String user, String password) {
		Connection conn = null;
		try {
			Class.forName("com.ibm.as400.access.AS400JDBCDriver");
			conn = DriverManager.getConnection("jdbc:as400://" + host, user, password);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return conn;
	}

	protected int executeForUpdate(String sql) throws Exception {
		int result = 0;
		Connection conn = getConnection();
		if (conn != null) {
			try {
				Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
				result = stmt.executeUpdate(sql);
			} catch (Exception e) {
				e.printStackTrace();
				throw e;
			} finally {
				// conn.close();
			}
		}
		return result;
	}

	protected ResultSet executeForQuery(String sql) throws Exception {
		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = getConnection();
		if (conn != null) {
			try {
				stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
				rs = stmt.executeQuery(sql);
			} catch (Exception e) {
				e.printStackTrace();
				throw e;
			} finally {
				// if (stmt != null)
				// stmt.close();
				// if (conn != null)
				// conn.close();
			}
		}
		return rs;
	}

}
